﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapQVW_ABPOS(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQVW_ABPOS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryQVW_ABPOS(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapQVW_ABPOS(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertQVW_ABPOS(dwData, 1);
            }
            UpdateQVW_ABPOS(dwLocal);

        }
        public void InsertQVW_ABPOS(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO  QVW_ABPOS "
                    + " SELECT 'V'||ABPAUFKRS||ABPAUFLFD AS KAUF,'V' AS AUFTYP,'V'||ABPAUFKRS||ABPAUFLFD||ABPAUFPOS AS PAUF,ABPAUFKRS AS PKRS,ABPAUFLFD AS PLFD,ABPAUFPOS AS PPOS,ABPWAKZ AS ARTST,ABPART AS PART,ABPVAR AS PVAR,ABPFARBE AS PCOLOR,ABPDESS AS PDESS,ABPCOLORIT AS PDCWY,ABPBESCHICH AS PFINISH,ABPGROE AS PSIZE,CASE WHEN dbms_random.value(0,1)<0.2 THEN 'L' WHEN dbms_random.value(0,1)>0.8 THEN 'D' ELSE 'M' END AS COLORGRP,ABPLIEFNR AS PLIEFNR,ABPSOLLMENGE2*0.01 AS PPMTR,ABPSOLLMENGE6*0.01 AS PPKG,ABPSOLLMENGE1*0.01 AS PQTY1,ABPSOLLMENGE2*0.01 AS PQTY2,ABPSOLLMENGE3*0.01 AS PQTY3,ABPSOLLMENGE4*0.01 AS PQTY4,ABPSOLLMENGE5*0.01 AS PQTY5,ABPSOLLMENGE6*0.01 AS PQTY6,ABPSOLLMENGE7*0.01 AS PQTY7,ABPSOLLMENGE8*0.01 AS PQTY8,ABPEALFM*0.01 AS PEMTR,ABPEAKG*0.01 AS PEKG,ABPLIEFDAT,SUBSTR(ABPLIEFDAT,1,4) AS DYEAR,SUBSTR(ABPLIEFDAT,5,2) AS DMONTH,SUBSTR(ABPLIEFDAT,7,2) AS DDAY,'Q'||CEIL(TO_NUMBER(SUBSTR(ABPLIEFDAT,5,2))/3) AS DQUARTER,ABPSTARTDAT,SUBSTR(ABPSTARTDAT,1,4) AS BYEAR,SUBSTR(ABPSTARTDAT,5,2) AS BMONTH,SUBSTR(ABPSTARTDAT,7,2) AS BDAY,'Q'||CEIL(TO_NUMBER(SUBSTR(ABPSTARTDAT,5,2))/3) AS BQUARTER,ABPENDDAT,SUBSTR(ABPENDDAT,1,4) AS EYEAR,SUBSTR(ABPENDDAT,5,2) AS EMONTH,SUBSTR(ABPENDDAT,7,2) AS EDAY,'Q'||CEIL(TO_NUMBER(SUBSTR(ABPENDDAT,5,2))/3) AS EQUARTER,ABPANRKR||ABPALFDNR AS PFLOW,ABPGESSTA AS PSTATUS,ABPSPGRD AS PSTATUSCODE,ABPWSSTA,ABPAPSTA,ABPRZSTA,ABPWPSTA,ABPWBSTA,ABPABSTA,ABPERLKZ AS PEND,ABPERLDAT AS FENDDAT,SUBSTR(ABPERLDAT,1,4) AS FYEAR,SUBSTR(ABPERLDAT,5,2) AS FMONTH,SUBSTR(ABPERLDAT,7,2) AS FDAY,'Q'||CEIL(TO_NUMBER(SUBSTR(ABPERLDAT,5,2))/3) AS FQUARTER,0.0 AS PFCOST,0.0 AS PMCOST,0.0 AS PMBOMCOST,0.0 AS PRODCOST,0.0 AS PROFIT,0.0 AS ARTCOSTKG,0.0 AS ARTCOSTMTR,0.0 AS ARTCOST,"
                    +"'Y' AS FOK,0.0 AS RTIME,0.0 AS RCOST "
                        //+ " FROM ABPOS WHERE ABPMANDANT=:MANDANT AND ABPNEUDAT>'20140701'";
                        + " FROM ABPOS WHERE ABPMANDANT=:MANDANT ";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteQVW_ABPOS(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QVW_ABPOS X  ";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryQVW_ABPOS(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM QVW_ABPOS"; ;
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "QVW_ABPOS";
            Return = Result;
            return Return;
        }
        public void UpdateQVW_ABPOS(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "  UPDATE QVW_ABPOS F SET (PMCOST,PMBOMCOST)=(SELECT SUM(KS.MCOST),SUM(KS.MBOMCOST) FROM QVW_ABFOLGE KS"
                        + " WHERE F.PAUF=KS.PAUF) WHERE EXISTS (SELECT 1 FROM QVW_ABFOLGE KS WHERE F.PAUF=KS.PAUF)";
                    break;
                case 2:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET PFCOST=(SELECT SUM(KS.FCOST) FROM QVW_ABFOLGE KS WHERE F.PAUF=KS.PAUF) WHERE EXISTS (SELECT 1 FROM QVW_ABFOLGE KS WHERE F.PAUF=KS.PAUF)";
                    break;
                case 3:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET ARTCOSTMTR=(SELECT MAX(STDCOST) FROM QVW_FWASTAMM KS WHERE RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND RTRIM(F.PART)=RTRIM(KS.PART)) WHERE EXISTS (SELECT 1 FROM QVW_FWASTAMM KS WHERE   F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART))";
                    break;
                case 4:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET ARTCOSTMTR=(SELECT MAX(STDCOST) FROM QVW_GWASTAMM KS WHERE  RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND RTRIM(F.PART)=RTRIM(KS.PART)) WHERE EXISTS (SELECT 1 FROM QVW_GWASTAMM KS WHERE  F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART)) ";
                    break;
                case 5:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET ARTCOSTMTR=(SELECT MAX(STDCOST) FROM QVW_FSSTAMM KS WHERE RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND RTRIM(F.PART)=RTRIM(KS.PART)) WHERE EXISTS (SELECT 1 FROM QVW_FSSTAMM KS WHERE   F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART))";
                    break;
                case 6:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET ARTCOSTKG=(SELECT MAX(STDCOST) FROM QVW_GSDAT KS WHERE  RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND RTRIM(F.PART)=RTRIM(KS.PART)) WHERE EXISTS (SELECT 1 FROM QVW_GSDAT KS WHERE F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART)) ";
                    break;
                case 7:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET PRODCOST=PMCOST+PFCOST,PROFIT=ARTCOSTMTR-(PMCOST+PFCOST),ARTCOST=ARTCOSTMTR WHERE ARTCOSTMTR>0 ";
                    break;
                case 8:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET PRODCOST=PMCOST+PFCOST,PROFIT=ARTCOSTKG-(PMCOST+PFCOST),ARTCOST=ARTCOSTKG WHERE ARTCOSTKG>0";
                    break;
                case 9:
                    or1.CommandText = "UPDATE QVW_ABPOS F SET (F.FOK,F.RTIME,F.RCOST)=(SELECT 'N', COUNT(*),SUM(PRODCOST) FROM QVW_ABFOLGE KS JOIN QVW_ABPOS R ON  KS.RPAUF=R.PAUF WHERE F.PAUF=KS.PAUF GROUP BY KS.PAUF)"
                        + " WHERE EXISTS (SELECT 1 FROM QVW_ABFOLGE KS JOIN QVW_ABPOS R ON  KS.RPAUF=R.PAUF WHERE F.PAUF=KS.PAUF ) ";
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
